Imports System.Data.OleDb.OleDbException
Imports System.IO.File
Imports System.Data.OleDb
Imports RIBS.Code.GlobalModule1

Namespace Code.DBCode
    Module ModDatabase
        Public gConn As New OleDbConnection
        Public gConnectionString As String
        Public gConnectionStringArchive As String
        Public DisplayDataBaseName As String = ""

        Function RepDataBase(ByVal DatabaseName As String) As Boolean
            'Dim db As New JRO.JetEngine
            'Dim sUFN As String
            'Dim DataPath As String = System.IO.Path.GetDirectoryName(DatabaseName)
            'Dim FileName As String = System.IO.Path.GetFileNameWithoutExtension(DatabaseName)
            'Try
            '    sUFN = DataPath & "\" & FileName & Format(Now, "MMdddyyyyHHmmss") & ".mdb"
            '    '                Copy(DatabaseName, sUFN, True)
            '    db.CompactDatabase(DatabaseName, sUFN)
            '    Rename(DatabaseName, sUFN)
            '    Return True
            'Catch ex As System.Exception
            '    Return False
            'End Try

        End Function
        Public Function CheckDataBase(Optional ByVal bCheckDatabase As Boolean = True) As Boolean
            Try
                'Dim Myset As New Settings

                If bCheckDatabase Then
                    If Not Exists(GetdbPath) Then
                        MsgBox("Database Not found! " & vbCrLf & "Please select Databse to use", MsgBoxStyle.Exclamation, "Database")
                        Dim Path As New OpenFileDialog
                        Path.Title = "Database"
                        Path.Filter = "Database (*.mdb)|*.mdb"
                        Path.ShowDialog()
                        My.Settings.AvailableDatabases = Path.FileName
                        My.Settings.Save()
                    End If
                Else
                    Dim Path As New OpenFileDialog
                    Path.Title = "Database"
                    Path.Filter = "Database (*.mdb)|*.mdb"
                    Path.ShowDialog()
                    My.Settings.AvailableDatabases = Path.FileName
                    My.Settings.Save()
                    gConn.ConnectionString = gConnectionString
                    gConn.Open()

                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Function GetDataTable(ByVal Sql As String) As DataTable
            Dim dt As New DataTable
            Try
                Dim Conn As New OleDbConnection(gConnectionString)
                Dim Adapter As New OleDbDataAdapter
                Dim cmd As New OleDbCommand

                Adapter.SelectCommand = New OleDbCommand(Sql, Conn)
                Adapter.Fill(dt)
                Return dt
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function GetdbPath() As String
            'Dim Myset As New Settings
            Dim AvailDbs() As String
            Dim AppPath As String
            Dim Dbs As String
            Try
                AppPath = Microsoft.VisualBasic.Replace(LCase(Application.StartupPath), "bin", "")
                AvailDbs = Split(My.Settings.AvailableDatabases, "|")
                Dbs = AvailDbs(0)
                Dbs = Mid(Dbs, InStr(Dbs, "}") + 1)
                Dbs = Microsoft.VisualBasic.Replace(Dbs, "[AppPath]", AppPath, , , CompareMethod.Binary)
            Catch ex As Exception
                Throw ex
            End Try
            Return Dbs
        End Function

        Public Function GetdbConnection() As String
            Dim dbConnectionString As String
            Dim AvailDbs() As String
            Dim AppPath As String = ""
            Dim Dbs As String
            Try
                If My.Settings.AvailableDatabases = "" Then
                    Dbs = "\\clauxnp00_ops\OPSVOL\OPS\Protection Services\RibsData\ribs.Mdb"
                    Dbs = Microsoft.VisualBasic.Replace(Dbs, "ribs.Mdb", My.Application.Info.AssemblyName & ".Mdb", , , CompareMethod.Text)
                Else
                    AvailDbs = Split(My.Settings.AvailableDatabases, "|")
                    Dbs = AvailDbs(0)
                    Dbs = Mid(Dbs, InStr(Dbs, "}") + 1)
                    Dbs = Microsoft.VisualBasic.Replace(Dbs, "[AppPath]", AppPath, , , CompareMethod.Binary)
                End If
                AppPath = Microsoft.VisualBasic.Replace(LCase(Application.StartupPath), "bin", "")

                dbConnectionString = Microsoft.VisualBasic.Replace(My.Settings.ConnectionString, "[DATABASE]", Dbs, , , CompareMethod.Binary)
                DisplayDataBaseName = Dbs
            Catch ex As Exception
                Throw ex
            End Try
            Return dbConnectionString
        End Function

        Function ExecNoError(ByVal Sql As String) As Int16
            Try
                ExecuteNonQuery(Sql)
                Return 0
            Catch ex As Exception
                Return 1
            End Try
        End Function

        Function CheckDbSoftSetting(ByVal dbVersion As Int32) As Boolean
            Try
                Select Case dbVersion

                    Case 1
                        ExecNoError("DELETE FROM RibsState Where RibsState = ''")
                        ExecNoError("DELETE FROM RibsState WHERE RibsState IS Null")
                        ExecNoError("INSERT INTO [Action] ([Action]) VALUES ('" & RIBSState.Allocated.ToString & "')")
                        ExecNoError("INSERT INTO [Action] ([Action]) VALUES ('" & RIBSState.Unallocated.ToString & "')")
                        ExecNoError("Delete From [Action] WHERE [Action] ='New Action'")
                        '
                        ExecNoError("UPDATE [Action] SET ISVisible = False Where [Action] = '" & RIBSState.Allocated.ToString & "' ")

                        ExecNoError("INSERT INTO RibsState (RibsState) VALUES('" & RIBSState.Allocated.ToString & "')")
                        ExecNoError("INSERT INTO RibsState (RibsState) VALUES('" & RIBSState.Unallocated.ToString & "')")
                        ExecNoError("INSERT INTO RibsState (RibsState) VALUES('Pending')")
                        ExecNoError("INSERT INTO RibsState (RibsState) VALUES('Closed')")

                        ExecNoError("INSERT INTO Users (UserID,UserName,LastLogin) VALUES('" & RIBSState.Unallocated.ToString & "', '" & RIBSState.Unallocated.ToString & "','" & Now() & "') ")
                        ExecNoError("Update Users SET LastLogin = '" & Now() & "' WHERE UserID = '" & RIBSState.Unallocated.ToString & "'")
                        ExecNoError("Update RIBS SET UserID = '" & RIBSState.Unallocated.ToString & "' & WHERE UserID = 'None'")
                        ExecNoError("DELETE FROM Users WHERE UserID = 'Melanie'")
                        ExecNoError("DELETE FROM Users WHERE UserID = 'None'")
                        If CheckTableValue("ContactMethod", "ContactMethod", "Adviser Incoming Phone") = "" Then
                            ExecNoError("INSERT INTO ContactMethod (ContactMethod) VALUES('Adviser Incoming Phone')")
                        End If
                        Dim aContactMethod() As String = Split("Adviser In-Email," & _
                                                                "Adviser In-Mail," & _
                                                                "Client In-Email," & _
                                                                "Client In-Mail," & _
                                                                "NAR (No Action Required)," & _
                                                                "Outgoing E-mail," & _
                                                                "Outgoing Mail," & _
                                                                "Other," & _
                                                                RIBSState.Unallocated.ToString, ",")
                        For Each str As String In aContactMethod
                            If CheckTableValue("ContactMethod", "ContactMethod", str.ToString) = "" Then
                                ExecNoError("INSERT INTO ContactMethod (ContactMethod) VALUES('" & str.ToString & "')")
                            End If
                        Next
                        ExecNoError("Update ActionHistory SET ContactMethod = 'Other' Where ContactMethod = 'System'")
                        ExecNoError("Delete FROM ContactMethod Where ContactMethod = 'System'")
                        Dim oRibs As New RibsClass
                        For Each dr As DataRow In GetDataTable("SELECT RibsID FROM RIBS WHERE UserID  Is Null ").Rows
                            oRibs.InsertNewAction(dr("RibsID"), RIBSState.Unallocated.ToString, "Imported", Now, RIBSState.Unallocated.ToString, RIBSState.Unallocated.ToString, "", RIBSState.Unallocated.ToString)
                            'oRibs.AllocateWork(dr("RibsID").ToString & "," & RIBSState.Unallocated.ToString & "|")
                        Next

                        For Each dr As DataRow In GetDataTable("SELECT DISTINCT Ribs.RibsID, ActionHistory.ActionID " & _
                                                            " FROM Ribs LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID " & _
                                                            " WHERE (((ActionHistory.ActionID) Is Null))").Rows
                            oRibs.InsertNewAction(dr("RibsID"), RIBSState.Unallocated.ToString, "Imported", Now, RIBSState.Unallocated.ToString, RIBSState.Unallocated.ToString, "", RIBSState.Unallocated.ToString)
                        Next
                End Select
                Beep()
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Function GetDBVersion() As Int32
            Try
                Dim Sql As String = "Select Top 1 * from dbVersion ORder By dbVersion Desc"
                Dim con As New OleDbConnection(gConnectionString)
                Dim cmd As New OleDbCommand(Sql, con)
                Dim Reader As OleDbDataReader
                con.Open()
                Reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
                If Reader.Read() Then
                    Return Reader("dbVersion")
                Else
                    Return False
                End If

                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

        Function CheckDbTables() As Boolean
            Dim lErr As Int64 = 0
            Dim dt As New DataTable
            Dim errCtr As Int32 = 0
            Dim errStr As String = ""
            Dim dbVersion As Int32 = 0
            Dim MaxdbVaresion As Int16 = 7
            Try
                Dim SQL As String = ""
                If Not CheckTable("DbVersion") Then
                    SQL = "CREATE TAble DbVersion (DbVersion INT, DT DateTime )"
                    If Not ExecNoError(SQL) Then
                        errCtr += 1
                        errStr += "Failed to create DB Version Table " & vbCrLf
                    End If
                    If Not ExecNoError("Insert INTO DbVersion (DbVersion, DT) VALUES (1,'" & Format(Now(), "dd MMM yyyy hh:mm") & "')") Then
                        errCtr += 1
                        errStr += "Failed to Update DB Version Table " & vbCrLf
                    End If
                End If
CheckDbVersion:
                If errCtr = 0 Then
                    dbVersion = GetDBVersion()
                End If
                Select Case dbVersion
                    Case 1
                        ExecNoError("ALTER TABLE RibsState ADD COLUMN [Explain] Bit NULL")
                        ExecNoError("Update RIBS Set UserID = '" & RIBSState.Unallocated.ToString & "' Where UserID ='None'")
                        ExecNoError("Update RIBS Set UserID = '" & RIBSState.Unallocated.ToString & "' Where UserID =''")
                        ExecNoError("Update RIBS Set UserID = '" & RIBSState.Unallocated.ToString & "' Where UserID is Null")
                        ExecNoError("Update ActionHistory Set [User] = '" & RIBSState.Unallocated.ToString & "' Where [User] ='None'")
                        ExecNoError("Update ActionHistory Set [Action] =' " & RIBSState.Unallocated.ToString & "' Where [Note] ='Allocated To: None'")
                        ExecNoError("Insert INTO DbVersion (DbVersion, DT) VALUES (2,'" & Format(Now(), "dd MMM yyyy hh:mm") & "')")
                    Case 2
                        ExecNoError("ALTER TABLE ContactMethod ADD COLUMN [Closed] Bit NULL")
                        If Not CheckTable("Reason") Then
                            SQL = "CREATE TAble Reason ([Reason] Text(50), [Closed] Bit )"
                            ExecNoError(SQL)
                            ExecNoError("Insert INTO Reason ([Reason] , [Closed] ) VALUES ('Test Reason 1',0)")
                            ExecNoError("Insert INTO Reason ([Reason] , [Closed] ) VALUES ('Test Reason 2',0)")
                            ExecNoError("ALTER TABLE ActionHistory ADD COLUMN [Reason] Text(50)")
                        End If
                        ExecNoError("Insert INTO DbVersion (DbVersion, DT) VALUES (3,'" & Format(Now(), "dd MMM yyyy hh:mm") & "')")
                    Case 3
                        lErr += ExecNoError("Create TABLE Groups  (GroupName Text(80) )")
                        lErr += ExecNoError("ALTER TABLE Groups ADD CONSTRAINT GroupName_PK PRIMARY KEY (GroupName)")
                        lErr += ExecNoError("Insert INTO Groups ( GroupName) VALUES ( 'Default' )")
                        lErr += ExecNoError("Insert INTO Groups ( GroupName) VALUES ( 'Standard' )")
                        lErr += ExecNoError("Insert INTO Groups ( GroupName) VALUES ( 'Supervisor' )")
                        ' 
                        lErr += ExecNoError("Create TABLE GroupPermission ( GroupName Text(80),  GroupPermission Text(80) )")
                        lErr += ExecNoError("ALTER TABLE GroupPermission ADD CONSTRAINT GroupPermission_GroupName_PK PRIMARY KEY  (GroupName, GroupPermission)")
                        lErr += ExecNoError("ALTER TABLE GroupPermission ADD CONSTRAINT FK_GroupPermission_Groups " & _
                                    " FOREIGN KEY ( GroupName ) REFERENCES Groups	( GroupName ) ")

                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Default', 'ViewData')")

                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Standard', 'AddHistory')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Standard', 'EditHistory')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Standard', 'DeleteHistory')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Standard', 'ViewData')")

                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'EnterWorkLoadScreen')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'AddHistory')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'DeleteHistory')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'DeleteOtherHistory')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'DeleteMainRec')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'EditHistory')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'EditOtherUserHistory')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'ViewData')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'EditUsers')")
                        lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Supervisor', 'SystemSettings')")

                        ''lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Super User', 'EnterWorkLoadScreen')")
                        'lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Super User', 'AddHistory')")
                        'lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Super User', 'DeleteHistory')")
                        'lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Super User', 'EditHistory')")
                        'lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Super User', 'EditOtherUserHistory')")
                        'lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Super User', 'Delete Main Record')")
                        'lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Super User', 'ViewData')")
                        'lErr += ExecNoError("Insert INTO GroupPermission (GroupName, GroupPermission) VALUES ('Super User', 'EditUsers')")

                        lErr += ExecNoError("Create TABLE UserGroup ( GroupName Text(80),  UserID Text(255) )")
                        lErr += ExecNoError("ALTER TABLE UserGroup ADD CONSTRAINT GroupPermission_GroupName_PK PRIMARY KEY  (GroupName, UserID)")
                        lErr += ExecNoError("ALTER TABLE UserGroup ADD CONSTRAINT FK_User_Groups " & _
                                    " FOREIGN KEY ( UserID ) REFERENCES Users ( UserID ) ")
                        lErr += ExecNoError("ALTER TABLE UserGroup ADD CONSTRAINT FK_UserGroups_Groups " & _
                                    " FOREIGN KEY ( GroupName ) REFERENCES Groups ( GroupName ) ")
                        lErr += ExecNoError("INSERT INTO UserGroup (UserID, GroupName) Select UserID, 'Default' From Users")
                        lErr += ExecNoError("INSERT INTO UserGroup (UserID, GroupName) Select UserID, 'Standard' From Users Where UserID not IN (Select userid from UserGroup where GroupName ='Standard')")
                        'lErr += ExecNoError("ALTER TABLE ActionHistory Note Memo")
                        If lErr = 0 Then
                            ExecNoError("Insert INTO DbVersion (DbVersion, DT) VALUES (4,'" & Format(Now(), "dd MMM yyyy hh:mm") & "')")
                        End If
                    Case 4
                        lErr += ExecNoError("INSERT INTO UserGroup (UserID, GroupName) Select UserID, 'Standard' From Users Where UserID not IN (Select userid from UserGroup where GroupName ='Standard')")
                        ExecNoError("Insert INTO DbVersion (DbVersion, DT) VALUES (5,'" & Format(Now(), "dd MMM yyyy hh:mm") & "')")
                        lErr = 0
                    Case 5
                        lErr = 0
                        lErr += ExecNoError("ALTER TABLE [Users] ADD COLUMN [MainFilter] Text(100)")
                        lErr += ExecNoError("ALTER TABLE [Users] ADD COLUMN [TaskFilter] Text(100)")
                        'If lErr = 0 Then
                        ExecNoError("Insert INTO DbVersion (DbVersion, DT) VALUES (6,'" & Format(Now(), "dd MMM yyyy hh:mm") & "')")
                        'End If
                        lErr = 0
                    Case 6
                        lErr = 0
                        lErr += ExecNoError("ALTER TABLE [RIBS] ADD COLUMN [AllocDate] DateTime ")
                        lErr += ExecNoError("ALTER TABLE [RIBS] ADD COLUMN [UnAllocDate] DateTime ")
                        If lErr = 0 Then
                            ExecNoError("Insert INTO DbVersion (DbVersion, DT) VALUES (7,'" & Format(Now(), "dd MMM yyyy hh:mm") & "')")
                        End If

                    Case 7
                End Select
                'GetUserWorkByDateAndAction

                If lErr <> 0 Then
                    MsgBox("Database upgrade has failed contact support!", MsgBoxStyle.Critical, "Error")
                End If
                CheckDbSoftSetting(dbVersion)
                If MaxdbVaresion < dbVersion Then
                    GoTo CheckDbVersion
                End If
                If errCtr <> 0 Then
                    MsgBox("Data fix error found! " & vbCrLf & " Please contact support " & vbCrLf & errStr)
                End If
            Catch ex As Exception
                Throw ex
            End Try
            Return True
        End Function

        Function CheckTableValue(ByVal lTable As String, ByVal lField As String, ByVal lValue As String) As String
            Try
                Dim Sql As String = "Select " & lField & " from [" & lTable & "] Where " & lField & " = '" & lValue & "'"
                Dim con As New OleDbConnection(gConnectionString)
                Dim cmd As New OleDbCommand(Sql, con)
                Dim Reader As OleDbDataReader
                con.Open()
                Reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
                If Reader.Read() Then
                    Return Reader(lField)
                Else
                    Return ""
                End If

                Return True
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Function CheckTable(ByVal lTable As String) As Boolean
            Try
                Dim Sql As String = "Select * from [" & lTable & "]"
                Dim con As New OleDbConnection(gConnectionString)
                Dim cmd As New OleDbCommand(Sql, con)
                Dim Reader As OleDbDataReader

                con.Open()
                Reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
                If Reader.Read() Then
                    Return True
                Else
                    Return False
                End If

                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Function CreateAccessDatabase(ByVal DatabaseFullPath As String) As Boolean
            'Dim bAns As Boolean
            ''Dim cat As New ADOX.Catalog()
            'Try
            '    Dim sCreateString As String
            '    sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseFullPath
            '    cat.Create(sCreateString)
            '    bAns = True
            'Catch Excep As System.Runtime.InteropServices.COMException
            '    bAns = False
            '    cat = Nothing
            'End Try
            'Return bAns
        End Function

        'Function CreateAccessDatabaseDoWork()
        '    'Try
        '    '    Dim SQL() As String = Split(condbCreate, "|")

        '    '    For Each cmd As String In SQL
        '    '        If CreateTable(cmd) Then
        '    '        Else
        '    '            Return False
        '    '        End If
        '    '    Next

        '    '    Return True
        '    'Catch ex As Exception
        '    '    Return False
        '    'End Try
        'End Function

        'Function CreateTable(ByVal SQL As String) As Boolean
        '    Try
        '        If Trim(SQL) = "" Then Return True
        '        Debug.Print(SQL)
        '        Dim Conn As New OleDbConnection(gConnectionString)
        '        Dim Adapter As New OleDbDataAdapter
        '        Dim cmd As New OleDbCommand
        '        Conn.Open()
        '        cmd.Connection = Conn
        '        cmd.CommandText = SQL
        '        cmd.ExecuteNonQuery()
        '        Return True
        '    Catch ex As Exception
        '        Debug.Print(ex.Message)
        '        Return False
        '    End Try
        'End Function

        Public Sub ExecuteNonQuery(ByVal sql As String)

            Dim con As New OleDbConnection(gConnectionString)
            Dim cmd As New OleDbCommand(sql, con)
            Try
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            Catch Err As OleDbException
                ' Use "caller inform" exception handling pattern.
                Throw Err
                'Throw New ApplicationException( _
                ' "Exception encountered when executing command.", Err)
            Finally
                con.Close()
            End Try

        End Sub

        Public Function ExecuteSQL(ByVal SQL As String, Optional ByVal TableName As String = "Table") As DataSet
            Dim ds As New DataSet
            Try
                Dim Conn As New OleDbConnection(gConnectionString)
                Dim Adapter As New OleDbDataAdapter
                Dim cmd As New OleDbCommand
                Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
                Adapter.Fill(ds, TableName)
            Catch ex As Exception
                Throw ex
            End Try
            Return ds
        End Function

    End Module




End Namespace
